use std::collections::HashMap;
use std::sync::Arc;

use libsql::{Builder, Connection, Database};
use serde::de::DeserializeOwned;
use tauri::{plugin::PluginApi, AppHandle, Runtime};
use tokio::sync::Mutex;
use uuid::Uuid;

use crate::error::{Error, Result};
use crate::models::*;

pub fn init<R: Runtime, C: DeserializeOwned>(
    app: &AppHandle<R>,
    _api: PluginApi<R, C>,
) -> crate::Result<Libsql<R>> {
    Ok(Libsql {
        app: app.clone(),
        connections: Arc::new(Mutex::new(HashMap::new())),
        databases: Arc::new(Mutex::new(HashMap::new())),
    })
}

type ConnectionsMap = HashMap<String, Connection>;
type DatabasesMap = HashMap<String, Database>;

/// Access to the libsql APIs.
pub struct Libsql<R: Runtime> {
    app: AppHandle<R>,
    connections: Arc<Mutex<ConnectionsMap>>,
    databases: Arc<Mutex<DatabasesMap>>,
}

impl<R: Runtime> Libsql<R> {
    pub async fn connect(&self, options: ConnectOptions) -> Result<String> {
        let db = if let Some(url) = options.url {
            if let Some(auth_token) = options.auth_token {
                // Create a remote replica
                Builder::new_remote_replica(options.local_path, url, auth_token)
                    .build()
                    .await?
            } else {
                // Create a local database
                Builder::new_local(options.local_path).build().await?
            }
        } else {
            Builder::new_local(options.local_path).build().await?
        };

        let conn = db.connect()?;
        let id = Uuid::new_v4().to_string();

        {
            let mut databases = self.databases.lock().await;
            databases.insert(id.clone(), db);
        }

        {
            let mut connections = self.connections.lock().await;
            connections.insert(id.clone(), conn);
        }

        Ok(id)
    }

    pub async fn execute(&self, options: ExecuteOptions) -> Result<u64> {
        let connection = {
            let connections = self.connections.lock().await;
            connections
                .get(&options.connection_id)
                .cloned()
                .ok_or_else(|| Error::ConnectionNotFound(options.connection_id.clone()))?
        };

        // Handle both positional and named parameters with correct libSQL format
        let result = if let Some(named_params_map) = options.named_params {
            // Use named parameters - libSQL expects Vec<(String, libsql::Value)>
            let converted_params = convert_named_params(Some(named_params_map))?;
            connection.execute(&options.sql, converted_params).await?
        } else if let Some(positional_params) = options.params {
            // Use positional parameters - libSQL expects Vec<libsql::Value>
            let params_vec = convert_positional_params(Some(positional_params))?;
            connection.execute(&options.sql, params_vec).await?
        } else {
            // No parameters
            connection.execute(&options.sql, ()).await?
        };

        Ok(result)
    }

    pub async fn query(&self, options: QueryOptions) -> Result<QueryResult> {
        let connection = {
            let connections = self.connections.lock().await;
            connections
                .get(&options.connection_id)
                .cloned()
                .ok_or_else(|| Error::ConnectionNotFound(options.connection_id.clone()))?
        };

        // Handle both positional and named parameters
        let mut rows = if let Some(named_params_map) = options.named_params {
            // Use named parameters - libSQL expects Vec<(String, libsql::Value)>
            let converted_params = convert_named_params(Some(named_params_map))?;
            connection.query(&options.sql, converted_params).await?
        } else if let Some(positional_params) = options.params {
            // Use positional parameters - libSQL expects Vec<libsql::Value>
            let params_vec = convert_positional_params(Some(positional_params))?;
            connection.query(&options.sql, params_vec).await?
        } else {
            // No parameters
            connection.query(&options.sql, ()).await?
        };

        let mut result_rows = Vec::new();
        let mut columns = Vec::new();

        if let Some(row) = rows.next().await? {
            // Get column names from the first row
            columns = (0..row.column_count())
                .filter_map(|i| row.column_name(i))
                .map(|s| s.to_string())
                .collect();

            // Process first row
            let values = (0..row.column_count())
                .map(|i| row.get_value(i).map(Value::from).unwrap_or(Value::Null))
                .collect();
            result_rows.push(values);

            // Process remaining rows
            while let Some(row) = rows.next().await? {
                let values = (0..row.column_count())
                    .map(|i| row.get_value(i).map(Value::from).unwrap_or(Value::Null))
                    .collect();
                result_rows.push(values);
            }
        }

        Ok(QueryResult {
            columns,
            rows: result_rows,
        })
    }

    pub async fn sync(&self, options: SyncOptions) -> Result<()> {
        // Get the database and sync it while holding the lock
        let databases = self.databases.lock().await;
        let database = databases
            .get(&options.connection_id)
            .ok_or_else(|| Error::ConnectionNotFound(options.connection_id.clone()))?;

        // Execute the libsql_sync() function via SQL
        let _ = database.sync().await?;

        Ok(())
    }

    pub async fn close(&self, options: CloseOptions) -> Result<()> {
        {
            let mut connections = self.connections.lock().await;
            connections.remove(&options.connection_id);
        }

        {
            let mut databases = self.databases.lock().await;
            databases.remove(&options.connection_id);
        }

        Ok(())
    }

    /// Execute a vector top-k nearest neighbor search
    pub async fn vector_top_k(&self, options: VectorTopKOptions) -> Result<QueryResult> {
        let connections = self.connections.lock().await;
        let connection = connections
            .get(&options.connection_id)
            .ok_or_else(|| Error::ConnectionNotFound(options.connection_id.clone()))?;

        // Prepare the query vector with the appropriate vector function
        let vector_type = options.vector_type.unwrap_or(VectorType::Float32);
        let vector_json = serde_json::to_string(&options.query_vector).map_err(|_| {
            Error::InvalidParameter(0, "Failed to serialize query vector".to_string())
        })?;

        // Build the vector_top_k query
        let sql = format!(
            "SELECT * FROM vector_top_k('{}', {}('{}'), {})",
            options.index_name,
            vector_type.as_function_name(),
            vector_json,
            options.k
        );

        // Execute the query
        let mut rows = connection.query(&sql, ()).await.unwrap();
        let mut result_rows = Vec::new();
        let mut columns = Vec::new();

        // Get column names from the first row if available
        if let Some(row) = rows.next().await.unwrap() {
            columns = (0..row.column_count())
                .map(|i| row.column_name(i).unwrap_or("").to_string())
                .collect();

            // Process first row
            let mut row_values = Vec::new();
            for i in 0..row.column_count() {
                let value = row.get::<libsql::Value>(i).unwrap();
                row_values.push(value.into());
            }
            result_rows.push(row_values);

            // Process remaining rows
            while let Some(row) = rows.next().await.unwrap() {
                let mut row_values = Vec::new();
                for i in 0..row.column_count() {
                    let value = row.get::<libsql::Value>(i).unwrap();
                    row_values.push(value.into());
                }
                result_rows.push(row_values);
            }
        }

        Ok(QueryResult {
            columns,
            rows: result_rows,
        })
    }

    /// Calculate cosine distance between two vectors
    pub async fn vector_distance_cos(
        &self,
        connection_id: String,
        vector1: Value,
        vector2: Value,
    ) -> Result<f64> {
        let connections = self.connections.lock().await;
        let connection = connections
            .get(&connection_id)
            .ok_or_else(|| Error::ConnectionNotFound(connection_id))?;

        // Convert vectors to SQL function calls
        let vec1_sql = vector1.to_vector_sql()?;
        let vec2_sql = vector2.to_vector_sql()?;

        let sql = format!("SELECT vector_distance_cos({}, {})", vec1_sql, vec2_sql);

        let mut rows = connection.query(&sql, ()).await.unwrap();
        if let Some(row) = rows.next().await.unwrap() {
            let distance: f64 = row.get(0).unwrap();
            Ok(distance)
        } else {
            Err(Error::QueryFailed(
                "No result from vector_distance_cos".to_string(),
            ))
        }
    }

    /// Calculate L2 (Euclidean) distance between two vectors
    pub async fn vector_distance_l2(
        &self,
        connection_id: String,
        vector1: Value,
        vector2: Value,
    ) -> Result<f64> {
        let connections = self.connections.lock().await;
        let connection = connections
            .get(&connection_id)
            .ok_or_else(|| Error::ConnectionNotFound(connection_id))?;

        // Convert vectors to SQL function calls
        let vec1_sql = vector1.to_vector_sql()?;
        let vec2_sql = vector2.to_vector_sql()?;

        let sql = format!("SELECT vector_distance_l2({}, {})", vec1_sql, vec2_sql);

        let mut rows = connection.query(&sql, ()).await.unwrap();
        if let Some(row) = rows.next().await.unwrap() {
            let distance: f64 = row.get(0).unwrap();
            Ok(distance)
        } else {
            Err(Error::QueryFailed(
                "No result from vector_distance_l2".to_string(),
            ))
        }
    }

    /// Extract vector from binary format to JSON array
    pub async fn vector_extract(
        &self,
        connection_id: String,
        vector_blob: Vec<u8>,
    ) -> Result<Vec<f32>> {
        let connections = self.connections.lock().await;
        let connection = connections
            .get(&connection_id)
            .ok_or_else(|| Error::ConnectionNotFound(connection_id))?;

        // Use vector_extract function to convert binary vector to text
        let sql = "SELECT vector_extract(?)";
        let mut rows = connection
            .query(sql, vec![libsql::Value::Blob(vector_blob)])
            .await
            .unwrap();

        if let Some(row) = rows.next().await.unwrap() {
            let json_str: String = row.get(0).unwrap();
            let vector_data: Vec<f32> = serde_json::from_str(&json_str).map_err(|_| {
                Error::InvalidParameter(0, "Failed to parse extracted vector".to_string())
            })?;
            Ok(vector_data)
        } else {
            Err(Error::QueryFailed(
                "No result from vector_extract".to_string(),
            ))
        }
    }

    /// Create a vector index on a table column
    pub async fn create_vector_index(
        &self,
        connection_id: String,
        index_name: String,
        table_name: String,
        column_name: String,
        settings: Option<Vec<String>>, // Optional settings like 'metric=l2', 'compress_neighbors=float8'
    ) -> Result<ExecuteResult> {
        let connections = self.connections.lock().await;
        let connection = connections
            .get(&connection_id)
            .ok_or_else(|| Error::ConnectionNotFound(connection_id))?;

        // Build the CREATE INDEX SQL with libsql_vector_idx
        let settings_str = if let Some(settings) = settings {
            format!(", '{}'", settings.join("', '"))
        } else {
            String::new()
        };

        let sql = format!(
            "CREATE INDEX {} ON {}(libsql_vector_idx({}{}))",
            index_name, table_name, column_name, settings_str
        );

        let rows_affected = connection.execute(&sql, ()).await.unwrap();
        Ok(ExecuteResult {
            rows_affected,
            last_insert_rowid: None, // Vector index creation doesn't return insert ID
        })
    }
}
